package co.iiitb.DAO;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.iiitb.model.Client;
import com.iiitb.model.Department;
import com.iiitb.model.Employee;
import com.iiitb.model.Project;
import com.iiitb.model.User_Login;
import com.iiitb.utils.MyLog;
import com.iiitb.utils.TMS_DB;

public class AdminDAO {

	public static List<Department> getAllDepartments() 
	{
		ArrayList<Department> selection = new ArrayList<Department>();
		ResultSet resultSet = null;
		String query = "select dept_id , name " +
				"from department " ;
		Connection connection = TMS_DB.getConnection();
		resultSet = TMS_DB.readFromDB(query, connection);
		try {
			while (resultSet.next()) {
				Department dept = getDepartmentFromResultSet(resultSet);
				selection.add(dept);
			}
		} catch (SQLException e) {
            MyLog.myCatch("AdminDAO.java", 33, e);
			e.printStackTrace();
		}
		TMS_DB.close(resultSet);
		TMS_DB.close(connection);
		return selection;
	}
	
	public static Department getDepartmentFromResultSet(ResultSet resultSet) {
		Department dp= new Department();
		try 
		{
			dp.setDeptId( resultSet.getInt("dept_id"));
			dp.setDeptName( resultSet.getString("name"));
			return dp;
		} 
		catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return dp;
	}
	public static List<Client>  getAllClients() 
	{
		ArrayList<Client> selection = new ArrayList<Client>();
		ResultSet resultSet = null;
		String query = "select user_id,client_id,client_name " +
						"from client " ;
		Connection connection = TMS_DB.getConnection();
		resultSet = TMS_DB.readFromDB(query,connection);
		try {
			while (resultSet.next()) {
				Client clt= getClientFromResultSet(resultSet);
				selection.add(clt);
			}
		} catch (SQLException e) {
            MyLog.myCatch("AdminDAO.java", 69, e);
			e.printStackTrace();
		}
		TMS_DB.close(resultSet);
		TMS_DB.close(connection);
		return selection;
	}
	
	public static Client getClientFromResultSet(ResultSet resultSet) 
	{
		Client clt=new Client();
		try 
		{
			clt.setUserId(resultSet.getInt("user_id"));
			clt.setClientId(resultSet.getInt("client_id"));
			clt.setClientName(resultSet.getString("client_name"));
			return clt;
		} 
		catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return clt;
	}
	
	public static List<Employee>  getAllEmployees() 
	{
		ArrayList<Employee> selection = new ArrayList<Employee>();
		ResultSet resultSet = null;
		String query = "select user_id,emp_id,name,dept_id,role,salary " +
						"from employee " ;
		Connection connection = TMS_DB.getConnection();
		resultSet = TMS_DB.readFromDB(query,connection);
		try {
			while (resultSet.next()) {
				Employee emp= getEmployeeFromResultSet(resultSet);
				selection.add(emp);
			}
		} catch (SQLException e) {
            MyLog.myCatch("AdminDAO.java", 108, e);
			e.printStackTrace();
		}
		TMS_DB.close(resultSet);
		TMS_DB.close(connection);
		return selection;
	}
	
	public static Employee getEmployeeFromResultSet(ResultSet resultSet) 
	{
		Employee emp=new Employee();
		try 
		{
			emp.setUserId(resultSet.getInt("user_id"));
			emp.setEmployeeId(resultSet.getInt("emp_id"));
			emp.setName(resultSet.getString("name"));
			emp.setDeptId(resultSet.getInt("dept_id"));
			emp.setRole(resultSet.getString("role"));
			emp.setSalary(resultSet.getInt("salary"));
			return emp;
		} 
		catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return emp;
	}
	
	public static List<Project> getAllProjects() 
	{
		ArrayList<Project> selection = new ArrayList<Project>();
		ResultSet resultSet = null;
		String query = "select project_id,pcode, name,client_id,pmgr_id,dept_id,budget,Man_hour_req " +
						"from project " ;
		Connection connection = TMS_DB.getConnection();
		resultSet = TMS_DB.readFromDB(query,connection);
		try {
			while (resultSet.next()) {
				Project proj = getProjectFromResultSet(resultSet);
				selection.add(proj);
			}
		} catch (SQLException e) {
            MyLog.myCatch("AdminDAO.java", 150, e);
			e.printStackTrace();
		}
		TMS_DB.close(resultSet);
		TMS_DB.close(connection);
		return selection;
	}
	
	public static Project getProjectFromResultSet(ResultSet resultSet) 
	{
		Project proj=new Project();
		try 
		{
			proj.setProjectId( resultSet.getInt("project_id"));
			proj.setProjectCode(resultSet.getString("pcode"));
			proj.setProjectName(resultSet.getString("name"));
			proj.setClientId(resultSet.getInt("client_id"));
			proj.setDeptId(resultSet.getInt("dept_id"));
			proj.setProjMgrId(resultSet.getInt("pmgr_id"));
			proj.setBudget(resultSet.getInt("budget"));
			proj.setMan_hour_req(resultSet.getInt("Man_hour_req"));
			return proj;
		} 
		catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return proj;
	}
	
	
	public static boolean checkProjectId(int projectId) 
	{
		String querry="select project_id, name " +
				"from project where project_id= "+projectId ;
		int rowCount;
		rowCount=TMS_DB.checkExsistence(querry);
		if(rowCount==0)
			return false;
		else
			return true;
	}
	
	public static int insert(String insertSql) 
	{
		Connection con=TMS_DB.getConnection();
		int rowCount;
		rowCount=TMS_DB.update(con,insertSql);
		return rowCount;
	}
	public static int getMaxId(String query) 
	{
		Connection con=TMS_DB.getConnection();
		int count=TMS_DB.getMaxId(query, con);
		return count;
	}
	
	public static int update(String updateSql) 
	{
		Connection con= TMS_DB.getConnection();
		int rowCount;
		rowCount=TMS_DB.update(con, updateSql);
		return rowCount;
	}
	
	public static List<User_Login> getAllClientUsers() 
	{
		ArrayList<User_Login> selection = new ArrayList<User_Login>();
		ResultSet resultSet = null;
		String query = "select user_id,username,pswd,role " +
				"from user_login " + "where role='client'" ;
		Connection connection = TMS_DB.getConnection();
		resultSet = TMS_DB.readFromDB(query, connection);
		try {
			while (resultSet.next()) {
				User_Login usr = UserDAO.getUserFromResultSet(resultSet);
				selection.add(usr);
			}
		} catch (SQLException e) {
            MyLog.myCatch("AdminDAO.java", 229, e);
			e.printStackTrace();
		}
		TMS_DB.close(resultSet);
		TMS_DB.close(connection);
		return selection;
	}
	
	public static List<User_Login> getAllEmployeeUsers() 
	{
		ArrayList<User_Login> selection = new ArrayList<User_Login>();
		ResultSet resultSet = null;
		String query = "select user_id,username,pswd,role " +
				"from user_login " + "where role IN ('Manager','Team Member','Admin')" ;
		Connection connection = TMS_DB.getConnection();
		resultSet = TMS_DB.readFromDB(query, connection);
		try {
			while (resultSet.next()) {
				User_Login usr = UserDAO.getUserFromResultSet(resultSet);
				selection.add(usr);
			}
		} catch (SQLException e) {
            MyLog.myCatch("AdminDAO.java", 251, e);
			e.printStackTrace();
		}
		TMS_DB.close(resultSet);
		TMS_DB.close(connection);
		return selection;
	}
	
	public static Client getClientById(int id) 
	{
		Client clt=new Client();
		ResultSet resultSet = null;
		String query = "select user_id,client_id,client_name " +
						"from client "+"where user_id="+id;
		Connection connection = TMS_DB.getConnection();
		resultSet = TMS_DB.readFromDB(query,connection);
		try {
			while (resultSet.next()) {
				clt= getClientFromResultSet(resultSet);
			}
		} catch (SQLException e) {
            MyLog.myCatch("AdminDAO.java", 272, e);
			e.printStackTrace();
		}
		TMS_DB.close(resultSet);
		TMS_DB.close(connection);
		return clt;
	}
	
	public static Employee getEmployeeById(int id) 
	{
		Employee emp=new Employee();
		ResultSet resultSet = null;
		String query = "select user_id,emp_id,name,dept_id,role,salary " +
						"from employee "+"where user_id="+id;
		Connection connection = TMS_DB.getConnection();
		resultSet = TMS_DB.readFromDB(query,connection);
		try {
			while (resultSet.next()) {
				emp= getEmployeeFromResultSet(resultSet);
			}
		} catch (SQLException e) {
            MyLog.myCatch("AdminDAO.java", 293, e);
			e.printStackTrace();
		}
		TMS_DB.close(resultSet);
		TMS_DB.close(connection);
		return emp;
	}
}
